﻿using System;
using System.Data;
using System.Data.SqlClient;


public class TransactionsDAL
    {
        dbConnection db = dbConnection.Instance;

        public  DataTable AccountFillDropDown()
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;
            DataSet dSet1 = null;
            SqlDataAdapter sda = null;

            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = db.GetCS();
                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select AccountId from [Accounts]";
                dSet1 = new DataSet();
                sda = new SqlDataAdapter(cmd);

                sda.Fill(dSet1, "Accounts");

                return dSet1.Tables["Accounts"];

            }

            catch
            {

                throw;

            }

            finally
            {

                dSet1.Dispose();

                sda.Dispose();

                cn.Close();

                cn.Dispose();

            }
        }

        public  int AddTransaction(TransactionsBO Transactions)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;
            int Counter = 0;

            SqlTransaction myTransaction = null; 

            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = db.GetCS();

                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
      
           
                cmd.CommandText = "INSERT INTO [Transactions] ( PayerAccountId, Amount,PayeeAccountId,TransactionDate) VALUES ( @PayerAccountId, @Amount,@PayeeAccountId,@TransactionDate)";
                cmd.CommandText = "  Declare @counter as integer Select @counter=count(*) from accounts where accountid=@PayeeAccountId if @counter=1 begin UPDATE [ACCOUNTS] Set Amount=Case AccountId   WHEN @PayerAccountId then @PayerAmount  when @PayeeAccountId then @PayeeAmount end WHERE AccountId in( @PayeeAccountId, @PayerAccountId ) INSERT INTO [Transactions] ( PayerAccountId, Amount,PayeeAccountId,TransactionDate) VALUES ( @PayerAccountId, @Amount,@PayeeAccountId,@TransactionDate) end ";
 
                cmd.Parameters.AddWithValue("@PayeeAccountId", Transactions.PayeeAccountId);
                cmd.Parameters.AddWithValue("@PayeeAmount", (Transactions.PayeeAmount) + (Transactions.Amount));
                cmd.Parameters.AddWithValue("@PayerAccountId", Transactions.PayerAccountId);
                cmd.Parameters.AddWithValue("@AccountId", Transactions.PayeeAccountId);
                
                decimal PayerAmount = (Transactions.PayerAmount) - (Transactions.Amount);

                cmd.Parameters.AddWithValue("@PayerAmount", PayerAmount);
                cmd.Parameters.AddWithValue("@Amount", Transactions.Amount);
                cmd.Parameters.AddWithValue("@TransactionDate", Transactions.TransactionDate);
                cn.Open();
                myTransaction = cn.BeginTransaction();// Assign command in the current transaction.SqlCommand myCommand = new SqlCommand();myCommand.Transaction = myTransaction;
                cmd.Transaction = myTransaction;
                
               
                 Counter = cmd.ExecuteNonQuery();
                 myTransaction.Commit();
            }
            catch (Exception ex)
            {
                myTransaction.Rollback();
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                    cn.Dispose();
                    cn = null;
                }
            }
            return (Counter);
        }


        public  int PayerUpdateAccount(TransactionsBO Transactions)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;
            int Counter = 0;


            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = db.GetCS();

                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "UPDATE [ACCOUNTS] SET  [Amount]=@PayerAmount WHERE PayerAccountId=@PayerAccountId";

                //cmd.Parameters.AddWithValue("@CategoryId", CategoryId);
                cmd.Parameters.AddWithValue("@PayerAccountId", Transactions.PayerAccountId);
                cmd.Parameters.AddWithValue("@PayerAmount", Transactions.PayerAmount);
              
               
                //cmd.Parameters.AddWithValue("@TransactionDate", Transactions.TransactionDate);
               

                cn.Open();
                Counter = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                    cn.Dispose();
                    cn = null;
                }
            }
            return (Counter);
        }

        public  int PayeeUpdateAccount(TransactionsBO Transactions)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;
            int Counter = 0;


            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = db.GetCS();

                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "UPDATE [ACCOUNTS] SET  [Amount]=@PayeeAmount WHERE AccountId=@PayeeAccountId";

                //cmd.Parameters.AddWithValue("@CategoryId", CategoryId);
                cmd.Parameters.AddWithValue("@PayeeAccountId", Transactions.PayeeAccountId);
                cmd.Parameters.AddWithValue("@PayeeAmount", Transactions.PayeeAmount);
                //cmd.Parameters.AddWithValue("@TransactionDate", Transactions.TransactionDate);


                cn.Open();
                Counter = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                    cn.Dispose();
                    cn = null;
                }
            }
            return (Counter);
        }

        public  decimal GetPayerAmount(TransactionsBO Transactions)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;


            decimal Counter = 0;

            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = db.GetCS();

                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select Amount from Accounts where AccountId=@PayerAccountId";
                cmd.Parameters.AddWithValue("@PayerAccountId", Transactions.PayerAccountId);
                cn.Open();
                Counter = ((decimal)cmd.ExecuteScalar());
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                    cn.Dispose();
                    cn = null;
                }
            }
            return Counter;
        }
        public decimal  GetPayeeAmount(TransactionsBO Transactions)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;
           

            decimal Counter = 0;

            try
            {
                cn = new SqlConnection();
                cn.ConnectionString = db.GetCS();

                cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "Select Amount from Accounts where AccountId=@PayeeAccountId";
               
                cmd.Parameters.AddWithValue("@PayeeAccountId", Transactions.PayeeAccountId);
               
                cn.Open();
                Counter = (decimal)cmd.ExecuteScalar();
                          }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
            finally
            {
                if (cn != null)
                {
                    cn.Close();
                    cn.Dispose();
                    cn = null;
                }
            }
            return Counter;
        }
   
    }

